"""
Case Type   : index blurred query
Case Name   : 设置schema指定支持字符集,table指定支持字符集,字段指定不支持字符集,验证字段模糊查询走索引
Create At   : 2024/10/15
Owner       : lvlintao666
Description :
    1、创建数据库
    2、创建schema
    3、创建表及索引
    4、查看执行计划及查询结果
    5、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、清理成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class IndeBlurredTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.db_name = "test_index_blurred"
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B' encoding = 'UTF8' " \
                         f" LC_CTYPE='zh_CN.UTF8' LC_COLLATE='zh_CN.UTF8';"
        self.create_sch = f"set client_encoding = 'UTF8';" \
                          f"create schema testsc charset UTF8;"
        self.create_tb = f"set client_encoding = 'UTF8';" \
                         f"create table testsc.testtb(c1 text charset sql_ascii collate 'fo_FO') charset UTF8;" \
                         f"insert into testsc.testtb values('sdeWEFWE,./;?:,./<>;?:[]！@#%&*+')," \
                         f" ('werWEFWE,./;?:,.L<>:/;?:[]！@#%&*+');" \
                         f"create index testindex on testsc.testtb(c1(20));"
        self.query_sql = f"set enable_seqscan=off;" \
                         f"set client_encoding = 'UTF8';" \
                         f"select * from testsc.testtb where c1 like 'sde%';" \
                         f"explain select * from testsc.testtb where c1 like 'sde%';"
        self.clean_tb = f"drop index testsc.testindex;" \
                        f"drop table testsc.testtb;" \
                        f"drop schema testsc;"
        self.clean_db = f"drop database {self.db_name};"
        self.flag1 = f"sdeWEFWE,./;?:,./<>;?:[]！@#%&*+"
        self.flag2 = f"Index Scan using testindex on testtb"

    def test_index(self):
        text = '--step1:创建数据库;expect:创建成功--'
        logger.info(text)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)

        text = '--step2:创建schema;expect:成功--'
        logger.info(text)
        sch_res = self.comsh.execut_db_sql(self.create_sch, dbname=self.db_name)
        logger.info(sch_res)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, sch_res,
                      '执行失败' + text)

        text = '--step3:创建表及索引;expect: 成功--'
        logger.info(text)
        tb_res = self.comsh.execut_db_sql(self.create_tb, dbname=self.db_name)
        logger.info(tb_res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, tb_res,
                      '执行失败' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, tb_res,
                      '执行失败' + text)

        text = '--step4:查看执行计划及查询结果;expect: 走索引--'
        logger.info(text)
        query_res = self.comsh.execut_db_sql(self.query_sql, dbname=self.db_name)
        logger.info(query_res)
        self.assertIn(self.flag1, query_res, '执行失败' + text)
        self.assertNotIn(self.flag2, query_res, '执行失败' + text)

    def tearDown(self):
        text = '--step5:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_INDEX_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
